const mysql = require('mysql2/promise');
require('dotenv').config({ path: '.env.local' });

async function updateDatabase() {
  try {
    console.log('正在连接到MySQL...');
    
    // 创建数据库连接
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '123456',
      database: process.env.DB_NAME || 'xiaoyoubao',
      port: Number(process.env.DB_PORT) || 3306
    });

    console.log('已连接到MySQL服务器');
    
    // 更新用户表结构 - 添加新字段
    console.log('正在更新用户表结构...');
    
    // 添加联系方式字段
    await connection.query(`
      ALTER TABLE users 
      ADD COLUMN phone VARCHAR(20) DEFAULT NULL,
      ADD COLUMN address VARCHAR(255) DEFAULT NULL,
      ADD COLUMN website VARCHAR(255) DEFAULT NULL;
    `).catch(err => {
      if (err.code === 'ER_DUP_FIELDNAME') {
        console.log('联系方式字段已存在，跳过添加');
      } else {
        throw err;
      }
    });
    
    // 添加教育经历字段
    await connection.query(`
      ALTER TABLE users 
      ADD COLUMN education_level VARCHAR(50) DEFAULT NULL,
      ADD COLUMN school VARCHAR(100) DEFAULT NULL,
      ADD COLUMN major VARCHAR(100) DEFAULT NULL,
      ADD COLUMN graduation_year INT DEFAULT NULL;
    `).catch(err => {
      if (err.code === 'ER_DUP_FIELDNAME') {
        console.log('教育经历字段已存在，跳过添加');
      } else {
        throw err;
      }
    });
    
    // 添加工作经历字段(简历相关)
    await connection.query(`
      ALTER TABLE users 
      ADD COLUMN title VARCHAR(100) DEFAULT NULL,
      ADD COLUMN skills TEXT DEFAULT NULL,
      ADD COLUMN resume_url VARCHAR(255) DEFAULT NULL;
    `).catch(err => {
      if (err.code === 'ER_DUP_FIELDNAME') {
        console.log('简历相关字段已存在，跳过添加');
      } else {
        throw err;
      }
    });
    
    // 添加个人综述字段
    await connection.query(`
      ALTER TABLE users 
      ADD COLUMN resume_title VARCHAR(255) DEFAULT NULL,
      ADD COLUMN resume_description TEXT DEFAULT NULL;
    `).catch(err => {
      if (err.code === 'ER_DUP_FIELDNAME') {
        console.log('个人综述字段已存在，跳过添加');
      } else {
        throw err;
      }
    });
    
    // 创建工作经历表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS work_experiences (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        company VARCHAR(100) NOT NULL,
        position VARCHAR(100) NOT NULL,
        start_date DATE NOT NULL,
        end_date DATE,
        is_current BOOLEAN DEFAULT FALSE,
        description TEXT,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );
    `);
    
    // 创建教育经历表（可以存储多条教育经历）
    await connection.query(`
      CREATE TABLE IF NOT EXISTS education_experiences (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        school VARCHAR(100) NOT NULL,
        degree VARCHAR(50) NOT NULL,
        field_of_study VARCHAR(100),
        start_date DATE,
        end_date DATE,
        description TEXT,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );
    `);
    
    // 创建技能表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS skills (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        name VARCHAR(50) NOT NULL,
        proficiency INT DEFAULT 3,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );
    `);
    
    // 创建实习经历表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS internships (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        company VARCHAR(100) NOT NULL,
        position VARCHAR(100) NOT NULL,
        period VARCHAR(100) NOT NULL,
        description TEXT,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );
    `);
    
    // 检查实习经历表是否存在旧字段，如存在则更新表结构
    const [columns] = await connection.query(`SHOW COLUMNS FROM internships LIKE 'start_date'`);
    if (columns.length > 0) {
      console.log('正在更新实习经历表结构...');
      // 添加新字段
      await connection.query(`ALTER TABLE internships ADD COLUMN period VARCHAR(100) DEFAULT NULL;`).catch(err => {
        if (err.code === 'ER_DUP_FIELDNAME') {
          console.log('period字段已存在，跳过添加');
        } else {
          throw err;
        }
      });
      
      // 更新数据，将start_date和end_date合并到period字段
      await connection.query(`
        UPDATE internships 
        SET period = CONCAT(DATE_FORMAT(start_date, '%Y-%m'), ' 至 ', 
                           IFNULL(DATE_FORMAT(end_date, '%Y-%m'), '至今'))
        WHERE period IS NULL;
      `).catch(err => {
        console.log('更新period数据时出错，可能没有现有数据:', err.message);
      });
      
      // 删除旧字段
      await connection.query(`
        ALTER TABLE internships 
        DROP COLUMN start_date,
        DROP COLUMN end_date;
      `).catch(err => {
        console.log('删除旧字段时出错:', err.message);
      });
    }
    
    // 创建获奖情况表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS awards (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        name VARCHAR(100) NOT NULL,
        issuer VARCHAR(100) NOT NULL,
        date DATE NOT NULL,
        description TEXT,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );
    `);
    
    // 检查是否需要更新用户表的自动时间戳
    try {
      await connection.query(`
        ALTER TABLE users
        MODIFY COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        MODIFY COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
      `);
      console.log('已更新用户表的时间戳为自动更新');
    } catch (err) {
      console.log('用户表时间戳字段已设置为自动更新或更新失败:', err.message);
    }
    
    console.log('数据库更新成功！');
    await connection.end();
    
  } catch (error) {
    console.error('更新数据库时出错:', error);
    process.exit(1);
  }
}

updateDatabase(); 